CREATE DATABASE "ions"
  WITH OWNER = "ax"
    ENCODING = 'UTF8'
    TABLESPACE = "pg_default";
--    CREATE SCHEMA "test" AUTHORIZATION "ax";

-- Table: format_sq

-- DROP TABLE format_s;
-- DROP SEQUENCE format_s_id_seq;
CREATE TABLE format_s
(
  id serial NOT NULL,
  measure_date timestamp without time zone,
  seans integer,
  nr0 integer,
  nr1 integer,
  power bigint[680],
  acf bigint[170][18],
  adc_null bigint[680],
  slabel integer[180],
  user_id character varying(40) DEFAULT "current_user"(),
  modifydate timestamp without time zone DEFAULT now(),
  CONSTRAINT pk_format_s PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);

ALTER TABLE format_s OWNER TO ax;

-- Table: format_v

-- DROP TABLE format_v;
-- DROP SEQUENCE format_v_id_seq;


CREATE TABLE format_v
(
  id serial NOT NULL,
  measure_date timestamp without time zone,
  seans integer,
  nr0 integer,
  nr1 integer,
  dat1 bigint[230][10],
  dat2 bigint[230][10],
  apower bigint[230],
  adc1_null bigint[230],
  adc2_null bigint[230],
  alabel integer[230],
  user_id character varying(40) DEFAULT "current_user"(),
  modifydate timestamp without time zone DEFAULT now(),
  CONSTRAINT pk_format_v PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE format_v OWNER TO ax;
create table format_snew (
   id                   SERIAL  NOT NULL,          
   measure_date         timestamp without time zone,
   seans                integer,              
   nr0                  integer,                 
   nr1                  integer,                 
   akf1                 bigint[680][19],     
   akf2                 bigint[680][19],      
   akf3                 bigint[680][19],      
   akf4                 bigint[680][19],     
   adc_null1            bigint[680],          
   adc_null2            bigint[680],           
   adc_null3            bigint[680],             
   adc_null4            bigint[680],            
   power1               bigint[680],            
   power2               bigint[680],
   user_id character varying(40) DEFAULT "current_user"(),
   modifydate timestamp without time zone DEFAULT now(),   
   CONSTRAINT pk_format_snew PRIMARY KEY (id)   
)

WITH (
  OIDS=FALSE
);
ALTER TABLE format_snew OWNER TO ax;

CREATE TABLE v2006(
	CHECK(measure_date >= date '2006-01-01' AND measure_date < date '2007-01-01')
    ) INHERITS (format_v);
CREATE TABLE s2008(
	CHECK(measure_date >= DATE '2008-01-01' AND measure_date < DATE '2009-01-01')
	) INHERITS (format_s);
CREATE TABLE s2009(
	CHECK(measure_date >= DATE '2009-01-01' AND measure_date < DATE '2010-01-01')
	) INHERITS (format_s);
	
	
	
CREATE OR REPLACE FUNCTION format_v_insert()
RETURNS TRIGGER AS $$
BEGIN
 IF(NEW.measure_date >= DATE '2006-01-01' AND 
 NEW.measure_date < DATE '2007-01-01' ) THEN
 INSERT INTO v2006 VALUES (NEW.*);
  ELSIF(NEW.measure_date >= DATE '2007-01-01' AND 
  NEW.measure_date <DATE '2008-01-01')THEN
  INSERT INTO v2007 VALUES(NEW.*);
 END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_v_trigger
 BEFORE INSERT ON format_v
  FOR EACH ROW EXECUTE PROCEDURE format_v_insert();
  
CREATE OR REPLACE FUNCTION format_s_insert()
RETURNS TRIGGER AS $$
BEGIN
 IF(NEW.measure_date >= DATE '2008-01-01' AND 
 NEW.measure_date < DATE '2009-01-01' ) THEN
 INSERT INTO s2008 VALUES (NEW.*);
  ELSIF(NEW.measure_date >= DATE '2009-01-01' AND 
  NEW.measure_date <DATE '2010-01-01')THEN
  INSERT INTO s2009 VALUES(NEW.*);
 END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

CREATE TRIGGER insert_s_trigger
 BEFORE INSERT ON format_s
  FOR EACH ROW EXECUTE PROCEDURE format_s_insert();
  
 
 CREATE INDEX s2008_measure_date ON s2008(measure_date);
CREATE INDEX s2009_measure_date ON s2009(measure_date);
CREATE INDEX v2006_measure_date ON v2006(measure_date);
 
  INSERT INTO format_s(
            measure_date, seans, nr0, nr1, power, acf, adc_null, slabel 
            )
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, 
            );
INSERT INTO format_snew(
            measure_date, seans, nr0, nr1, akf1, akf2, akf3, akf4, adc_null1, 
            adc_null2, adc_null3, adc_null4, power1, power2)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, 
            ?, ?, ?, ?, ?);
INSERT INTO format_v(
            measure_date, seans, nr0, nr1, dat1, dat2, apower, adc1_null, 
            adc2_null, alabel)
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, 
            ?, ?);